<?php
class LiveModel extends BaseModel
{
    /**
     * @return CDbConnection
     */
    protected static function getDb()
    {
        return Yii::app()->db_live;
    }

    public static function getTotalUser($date)
    {
        $sql = 'SELECT count(*)
                FROM users u
                WHERE DATE(u.created_time) <= :date';
        $command = self::getDb()->createCommand($sql);
        $command->bindParam(':date', $date);
        return (int) $command->queryScalar();
    }

    public static function getTotalByGender($date, $gender)
    {
        $sql = 'SELECT count(*)
                FROM users u
                WHERE u.gender = :gender AND DATE(u.created_time) <= :date';
        $command = self::getDb()->createCommand($sql);
        $command->bindParam(':gender', $gender);
        $command->bindParam(':date', $date);
        return (int) $command->queryScalar();
    }

    public static function getTotalUserPerCountry($date, $limit = 10)
    {
        $sql = 'SELECT
                    c.country AS `code`,
                    c.country_name AS `name`,
                    COUNT(*) AS `amount`,
                    SUM(IF(u.gender = :gemder_male, 1, 0)) AS `amount_male`,
                    SUM(IF(u.gender = :gemder_female, 1, 0)) AS `amount_female`
                FROM users u
                JOIN user_coordinate uc ON u.user_id = uc.user_id
                JOIN city c ON uc.city_id = c.id
                WHERE DATE(u.created_time) <= :date
                GROUP BY c.country
                ORDER BY amount DESC
                LIMIT :limit';
        $command = self::getDb()->createCommand($sql);
        $command->bindParam(':gemder_male', $male = ENUM_GENDER_MALE);
        $command->bindParam(':gemder_female', $female = ENUM_GENDER_FEMALE);
        $command->bindParam(':date', $date);
        $command->bindParam(':limit', $limit);
        return $command->queryAll();
    }

    public static function getNewUserPerCountry($date, $limit = 10)
    {
        $sql = 'SELECT
                    c.country AS `code`,
                    c.country_name AS `name`,
                    COUNT(*) AS `amount`,
                    SUM(IF(u.gender = :gemder_male, 1, 0)) AS `amount_male`,
                    SUM(IF(u.gender = :gemder_female, 1, 0)) AS `amount_female`
                FROM users u
                JOIN user_coordinate uc ON u.user_id = uc.user_id
                JOIN city c ON uc.city_id = c.id
                WHERE DATE(u.created_time) = :date
                GROUP BY c.country
                ORDER BY amount DESC
                LIMIT :limit';
        $command = self::getDb()->createCommand($sql);
        $command->bindParam(':gemder_male', $male = ENUM_GENDER_MALE);
        $command->bindParam(':gemder_female', $female = ENUM_GENDER_FEMALE);
        $command->bindParam(':date', $date);
        $command->bindParam(':limit', $limit);
        return $command->queryAll();
    }

    public static function getTotalVerification()
    {
        $sql = 'SELECT COUNT(*)
                FROM users u
                WHERE u.is_verify = 1';
        $command = self::getDb()->createCommand($sql);
        return (int) $command->queryScalar();
    }

    public static function getTotalIOSVerification()
    {
        $sql = 'SELECT COUNT(*)
                FROM users u
                WHERE u.is_verify = 1 AND
                      u.device_token IS NOT NULL AND
                      u.android_token IS NULL';
        $command = self::getDb()->createCommand($sql);
        return (int) $command->queryScalar();
    }

    public static function getTotalAndroidVerification()
    {
        $sql = 'SELECT COUNT(*)
                FROM users u
                WHERE u.is_verify = 1 AND
                      u.device_token IS NULL AND
                      u.android_token IS NOT NULL';
        $command = self::getDb()->createCommand($sql);
        return (int) $command->queryScalar();
    }

    public static function getTotalEnabledUser()
    {
        $sql = 'SELECT COUNT(*)
                FROM users u
                WHERE u.is_enabled = 1';
        $command = self::getDb()->createCommand($sql);
        return (int) $command->queryScalar();
    }

    public static function getTotalUserGroupByAgeRange($date)
    {
        $sql = 'SELECT
                    SUM(IF(age < 20, 1, 0)) AS "<20",
                    SUM(IF(20 <= age AND age <= 25, 1, 0)) AS "20-25",
                    SUM(IF(26 <= age AND age <= 30, 1, 0)) AS "26-30",
                    SUM(IF(30 < age, 1, 0)) AS ">30"
                FROM
                (
                    SELECT YEAR(CURDATE()) - YEAR(u.birthday) AS age
                    FROM users u
                    WHERE DATE(u.created_time) <= :date
                ) t';
        $command = self::getDb()->createCommand($sql);
        $command->bindParam(':date', $date);
        return $command->queryRow();
    }

    public static function getTotalUserPlayedSnapshot($date)
    {
        $sql = 'SELECT COUNT(DISTINCT l.user_id)
                FROM likes l
                WHERE DATE(l.created_time) <= :date';
        $command = self::getDb()->createCommand($sql);
        $command->bindParam(':date', $date);
        return (int) $command->queryScalar();
    }
}
